package cn.exrick.xboot.chart;

import cn.exrick.xboot.index.mapper.IndexMapper;
import cn.exrick.xboot.modules.assessment.dao.mapper.HkAssessmentScoreMapper;
import cn.exrick.xboot.modules.assessment.service.IHkAssessmentScoreService;
import cn.exrick.xboot.modules.content.dao.HontLogDao;
import cn.exrick.xboot.modules.content.dao.mapper.HontUserMapper;
import cn.exrick.xboot.modules.content.entity.HontDepartment;
import cn.exrick.xboot.modules.content.entity.HontPost;
import cn.exrick.xboot.modules.content.entity.HontUser;
import cn.exrick.xboot.modules.content.service.HontDepartmentService;
import cn.exrick.xboot.modules.content.service.HontPostService;
import cn.exrick.xboot.modules.content.service.HontUserService;
import cn.exrick.xboot.modules.content.service.mybatis.IHontUserService;
import com.spire.xls.*;
import com.spire.xls.charts.ChartSerie;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.io.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import static cn.exrick.xboot.modules.base.utils.DateUtils.getMonthFirstDate;
import static cn.exrick.xboot.modules.base.utils.DateUtils.getMonthLastDate;

@RequestMapping("/xboot/Chart")
@Controller
@Slf4j
public class CreateChartController {
    @Autowired
    private HontUserMapper hontUserMapper;
    @Autowired
    private HkAssessmentScoreMapper hkAssessmentScoreMapper;
    @Autowired
    private HontLogDao hontLogDao;
    @Autowired
    private IndexMapper indexMapper;
    /**
     * https://www.e-iceblue.cn/spirexls_java_chart/create-excel-pie-charts-in-java.html
     * @param args
     */
    @RequestMapping(value = "/createChartSetData", method = RequestMethod.GET)
    @ResponseBody
    public void createChartSetData(String beginDate, String endDate,HttpServletResponse response) throws UnsupportedEncodingException {
        List<Map<String,Object>> list=hontUserMapper.getAssessmentTeam();
        for (Map<String,Object> item:list){
            List<String> ids=hontUserMapper.getIdsByDepartmentId(item.get("id").toString());
            //考核人数
            item.put("inspectionPeoPleNum",ids.size());
            int inspectionNum=hkAssessmentScoreMapper.selectCountByHontUserOnDepartementId(beginDate,endDate,item.get("id").toString());
            //考核次数
            item.put("inspectionNum",inspectionNum);
        }

        log.info("{}",list);
        createChart(list,beginDate,endDate,response,"汇总表");
    }
    @RequestMapping(value = "/createChartSetDataTo", method = RequestMethod.GET)
    @ResponseBody
    public void createChartSetDataTo(String monthDate,HttpServletResponse response) throws UnsupportedEncodingException {
        DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        monthDate = monthDate + "-01";
        LocalDate beginTime = getMonthFirstDate(monthDate, "yyyy-MM-dd");
        LocalDate endTime = getMonthLastDate(monthDate, "yyyy-MM-dd");
        String beginDate = fmt.format(beginTime);
        String endDate = fmt.format(endTime);
        List<Map<String,Object>> list=hontUserMapper.getAssessmentTeam();
        for (Map<String,Object> item:list){
            List<String> ids=hontUserMapper.getIdsByDepartmentId(item.get("id").toString());
            item.put("inspectionPeoPleNum",ids.size());
            int inspectionNum=hkAssessmentScoreMapper.selectCountByHontUserOnDepartementId(beginDate,endDate,item.get("id").toString());
            item.put("inspectionNum",inspectionNum);
        }

        log.info("{}",list);
        createChart(list,beginDate,endDate,response,monthDate + "汇总表.xlsx");
    }

    public void createChart(List<Map<String,Object>> list,String beginDate, String endDate,HttpServletResponse response,String fileName) throws UnsupportedEncodingException {
//        ArrayList<String> list =new ArrayList<>();
//        list.add("煅烧车间");list.add("煅烧车间");list.add("煅烧车间");list.add("煅烧车间");list.add("煅烧车间");list.add("煅烧车间");
//        list.add("成型车间");list.add("成型车间");list.add("成型车间");list.add("成型车间");list.add("成型车间");list.add("成型车间");
//        list.add("焙烧一车间");list.add("焙烧一车间");list.add("焙烧一车间");list.add("焙烧一车间");list.add("焙烧一车间");
//        list.add("焙烧二车间");list.add("焙烧二车间");list.add("焙烧二车间");list.add("焙烧二车间");list.add("焙烧二车间");
//        list.add("生产设备部");list.add("生产设备部");list.add("生产设备部");list.add("生产设备部");list.add("生产设备部");

       int mouth= getMonthFirstDate(beginDate, "yyyy-MM-dd").getMonthValue();
       String mouthStr=mouth+"月车间绩效考核";
        Color color_green = new Color(0, 142, 106);
        /**
         * 创建Workbook对象
         */
        Workbook workbook = new Workbook();
        /**
         * 获取第一个工作表
         */
        Worksheet sheet = workbook.getWorksheets().get(0);
        /**
         * TODO 将图表数据写入工作表
         */
        sheet.getCellRange("A1").setValue("绩效考核");
        sheet.getCellRange("A2").setValue(mouthStr);
        sheet.getCellRange("A3").setValue("车间");
        sheet.getCellRange("B3").setValue("班组");
        sheet.getCellRange("C3").setValue("班长/负责人");
        sheet.getCellRange("D3").setValue("考核人数");
        sheet.getCellRange("E3").setValue("考核次数");
        sheet.getCellRange("G2").setValue("数据统计");
        sheet.getCellRange("G4").setValue("绩效登录人数趋势");
        sheet.getCellRange("G24").setValue("绩效考核次数趋势");
//        sheet.getCellRange("G44").setValue("绩效考核平均时间 (车间)");
//
//        sheet.getCellRange("S4").setValue("绩效审批超时统计");
//        sheet.getCellRange("S24").setValue("绩效审批超时明细");

//        List<String> list2=list.stream().map(item->item.get("id").toString()).collect(Collectors.toList()).stream().distinct().collect(Collectors.toList());
//        List<String> list2= list.stream().distinct().collect(Collectors.toList());
        List<String> list2=list.stream().map(item->item.get("workshop").toString()).collect(Collectors.toList()).stream().distinct().collect(Collectors.toList());
        //遍历车间，并合并单元格
        long row=4;
        for (String s:list2){
            long count=list.stream().filter(item->s.equals(item.get("workshop").toString())).count();
            sheet.getCellRange("A"+row).setValue(s);
            sheet.getRange().get("A"+row+":A"+(row+count-1)).merge();
            row+=count;
        }
        //遍历考核数据
        long i=4;
        for (Map<String,Object>item:list){
            sheet.getCellRange("B"+(i)).setValue(item.get("title").toString());
            sheet.getCellRange("C"+(i)).setValue(item.get("nickName").toString());
            sheet.getCellRange("D"+(i)).setValue(item.get("inspectionPeoPleNum").toString());
            sheet.getCellRange("E"+(i)).setValue(item.get("inspectionNum").toString());
            i++;
        }


        /**
         * 按范围合并单元格,并设置行高
         */
        //合并单元格
        sheet.getRange().get("A1:V1").merge();
        sheet.getRange().get("A2:E2").merge();
        sheet.getRange().get("G2:V2").merge();
        sheet.getRange().get("G4:Q5").merge();
        sheet.getRange().get("G24:Q25").merge();
//        sheet.getRange().get("G44:Q45").merge();
//        sheet.getRange().get("S4:V5").merge();
//        sheet.getRange().get("S24:V25").merge();

        //设置行高
        sheet.setRowHeight(1,25);
        sheet.setRowHeight(2,20);
        //设置行高、列宽为自适应（应用于指定数据范围）
//        sheet.getAllocatedRange().autoFitRows();
//        sheet.getAllocatedRange().autoFitColumns();
        /**
         * 填充颜色
         */
        sheet.getRange().get("A1:V1").getStyle().setColor(color_green);
        sheet.getRange().get("A3:E3").getStyle().setColor(color_green);
        /**
         * 设置字体
         */

        sheet.getCellRange("A1").getCellStyle().getExcelFont().setFontName("Comic Sans MS");
        sheet.getCellRange("A1").getCellStyle().getExcelFont().setColor(Color.white);
        sheet.getCellRange("A1").getCellStyle().getExcelFont().setSize(16);
        sheet.getCellRange("A1").getCellStyle().getExcelFont().isBold(true);
        sheet.getCellRange("A2").getCellStyle().getExcelFont().setColor(color_green);
        sheet.getCellRange("A2").getCellStyle().getExcelFont().isBold(true);
        sheet.getCellRange("G2").getCellStyle().getExcelFont().setColor(color_green);
        sheet.getCellRange("G2").getCellStyle().getExcelFont().isBold(true);
        sheet.getCellRange("A3:E3").getCellStyle().getExcelFont().setColor(Color.white);
        //图表字体设置
        sheet.getCellRange("G4").getCellStyle().getExcelFont().setColor(color_green);
        sheet.getCellRange("G4").getCellStyle().getExcelFont().isBold(true);
        sheet.getCellRange("G24").getCellStyle().getExcelFont().setColor(color_green);
        sheet.getCellRange("G24").getCellStyle().getExcelFont().isBold(true);
//        sheet.getCellRange("G44").getCellStyle().getExcelFont().setColor(color_green);
//        sheet.getCellRange("G44").getCellStyle().getExcelFont().isBold(true);
//        sheet.getCellRange("S4").getCellStyle().getExcelFont().setColor(color_green);
//        sheet.getCellRange("S4").getCellStyle().getExcelFont().isBold(true);
//        sheet.getCellRange("S24").getCellStyle().getExcelFont().setColor(color_green);
//        sheet.getCellRange("S24").getCellStyle().getExcelFont().isBold(true);

        /**
         * 将合并单元格的对齐方式设置
         */
//        sheet.getRange().get("A2").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Left);
        //水平对齐居中
        sheet.getAllocatedRange().getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
        //垂直对齐方式设置为居中
        sheet.getAllocatedRange().getCellStyle().setVerticalAlignment(VerticalAlignType.Center);

        /**
         * 获取单元格，设置内边框、外边框
         */
        CellRange range1 = sheet.getCellRange("A2:E2");
        range1.borderInside(LineStyleType.Thin, color_green);
        range1.borderAround(LineStyleType.Medium, color_green);

        CellRange range2 = sheet.getCellRange("G2:V2");
        range2.borderInside(LineStyleType.Thin, color_green);
        range2.borderAround(LineStyleType.Medium, color_green);

        //绩效登录人数位置
        CellRange range3 = sheet.getCellRange("G4:Q5");
        range3.borderInside(LineStyleType.Thin, color_green);
        range3.borderAround(LineStyleType.Medium, color_green);
        CellRange range4 = sheet.getCellRange("G6:Q21");
        range4.borderInside(LineStyleType.Thin, color_green);
        range4.borderAround(LineStyleType.Medium, color_green);
        //绩效考核次数
        CellRange range5 = sheet.getCellRange("G24:Q25");
        range5.borderInside(LineStyleType.Thin, color_green);
        range5.borderAround(LineStyleType.Medium, color_green);
        CellRange range6 = sheet.getCellRange("G26:Q41");
        range6.borderInside(LineStyleType.Thin, color_green);
        range6.borderAround(LineStyleType.Medium, color_green);
//        //绩效考核平均时间（车间）
//        CellRange range7 = sheet.getCellRange("G44:Q45");
//        range7.borderInside(LineStyleType.Thin, color_green);
//        range7.borderAround(LineStyleType.Medium, color_green);
//        CellRange range8 = sheet.getCellRange("G46:Q61");
//        range8.borderInside(LineStyleType.Thin, color_green);
//        range8.borderAround(LineStyleType.Medium, color_green);
//        //绩效审批超时统计
//        CellRange range9 = sheet.getCellRange("S4:V5");
//        range9.borderInside(LineStyleType.Thin, color_green);
//        range9.borderAround(LineStyleType.Medium, color_green);
//        CellRange range10 = sheet.getCellRange("S6:V21");
//        range10.borderInside(LineStyleType.Thin, color_green);
//        range10.borderAround(LineStyleType.Medium, color_green);
//        //绩效审批超时明细
//        CellRange range11 = sheet.getCellRange("S24:V25");
//        range11.borderInside(LineStyleType.Thin, color_green);
//        range11.borderAround(LineStyleType.Medium, color_green);
        /**
         * 添加图表
         */
        //定图表类型
        Chart chart = sheet.getCharts().add(ExcelChartType.ColumnClustered);
        Chart chart1 = sheet.getCharts().add(ExcelChartType.LineMarkersStacked);
//        Chart chart2 = sheet.getCharts().add(ExcelChartType.ColumnClustered);
        number_Of_Trend(chart,beginDate,endDate);
        number_Of_assess( chart1,beginDate,endDate);
//        number_Of_avg( chart2,beginDate,endDate);
        //保存文档
        workbook.saveToFile("output/汇总表.xlsx", ExcelVersion.Version2016);
        downloadFile("output/汇总表.xlsx",response,fileName);
    }
    public void number_Of_Trend( Chart chart,String beginDate, String endDate){
        //设置图表位置
        chart.setLeftColumn(7);
        chart.setRightColumn(18);
        chart.setTopRow(6);
        chart.setBottomRow(22);

        //设置图表标题
        chart.setChartTitle("绩效登录人数趋势");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);
        //添加图表系列
        ChartSerie series1 = chart.getSeries().add();
        //定义图表生成所需数据
        //登录人数
        List<Map<String,Object>> data=hontLogDao.getHontLog(beginDate,endDate);
        List<Object> labels=data.stream().map(item->item.get("createTime")).collect(Collectors.toList());
        List<Object> values=data.stream().map(item->item.get("count")).collect(Collectors.toList());
        series1.setEnteredDirectlyCategoryLabels(labels.toArray());
        series1.setEnteredDirectlyValues(values.toArray());
//        series1.setEnteredDirectlyCategoryLabels(new Object[]{"英国", "美国", "中国", "加拿大", "泰国", "新加坡"});
//        series1.setEnteredDirectlyValues(new Object[]{254, 221, 438, 158, 250, 300});

//        chart.getPrimaryCategoryAxis().setTitle("原产国");
//        chart.getPrimaryValueAxis().setTitle("产 量");
        chart.getPlotArea().getFill().setForeColor(new Color(250,235,215));
    }
    public void number_Of_assess( Chart chart,String beginDate, String endDate){
        //设置图表位置
        chart.setLeftColumn(7);
        chart.setRightColumn(18);
        chart.setTopRow(26);
        chart.setBottomRow(42);
        //添加图表系列
        ChartSerie series1 = chart.getSeries().add();
        String dateType="%Y-%m-%d";
        List<Map<String, Object>> totalInstanceinfoCountDay= indexMapper.totalInstanceinfoCount(dateType,beginDate,endDate);
        List<Object> labels=totalInstanceinfoCountDay.stream().map(item->item.get("time")).collect(Collectors.toList());
        List<Object> values=totalInstanceinfoCountDay.stream().map(item->item.get("count")).collect(Collectors.toList());
        //定义图表生成所需数据
        series1.setEnteredDirectlyCategoryLabels(labels.toArray());
        series1.setEnteredDirectlyValues(values.toArray());
//        series1.setEnteredDirectlyCategoryLabels(new Object[]{"英国", "美国", "中国", "加拿大", "泰国", "新加坡"});
//        series1.setEnteredDirectlyValues(new Object[]{254, 221, 438, 158, 250, 300});

        //设置图表标题、坐标轴、背景色
        chart.getPlotArea().getFill().setForeColor(new Color(250,235,215));
    }
    public void number_Of_avg( Chart chart,String beginDate, String endDate){
        //设置图表位置
        chart.setLeftColumn(7);
        chart.setRightColumn(18);
        chart.setTopRow(46);
        chart.setBottomRow(62);
        //添加图表系列
        ChartSerie series1 = chart.getSeries().add();
        List<Map<String, Object>> countIndexBottomAvg= indexMapper.countIndexBottomAvg(beginDate,endDate);
        List<Object> labels=countIndexBottomAvg.stream().map(item->item.get("title")).collect(Collectors.toList());
        List<Object> values=countIndexBottomAvg.stream().map(item->item.get("time")).collect(Collectors.toList());
        //定义图表生成所需数据
        series1.setEnteredDirectlyCategoryLabels(labels.toArray());
        series1.setEnteredDirectlyValues(values.toArray());
//        series1.setEnteredDirectlyCategoryLabels(new Object[]{"英国", "美国", "中国", "加拿大", "泰国", "新加坡"});
//        series1.setEnteredDirectlyValues(new Object[]{254, 221, 438, 158, 250, 300});

        //设置图表标题、坐标轴、背景色
        chart.getPlotArea().getFill().setForeColor(new Color(250,235,215));
    }
    public static void downloadFile( String path,HttpServletResponse response, String filename) throws UnsupportedEncodingException {
        File file = new File(path);
        response.setContentType("application/msexcel");
        response.reset();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "iso8859-1"));
        byte[] buffer = new byte[1024];
        FileInputStream fis = null; //文件输入流
        BufferedInputStream bis = null;

        OutputStream os = null; //输出流
        try {
            os = response.getOutputStream();
            fis = new FileInputStream(file);
            bis = new BufferedInputStream(fis);
            int y = bis.read(buffer);
            while (y != -1) {
                os.write(buffer);
                y = bis.read(buffer);
            }

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        try {
            bis.close();
            fis.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
//删除缓存和文件
        file.delete();
    }
}
